package com.mycompany.inventory.persistence;

import com.mycompany.common.AbstractDao;
import com.mycompany.common.jdbc.ConnectionDatabase;
import com.mycompany.inventory.pojo.StorageInfo;

import java.util.ArrayList;
import java.util.List;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * Created by My Company.
 * User: loitran
 * Date: Feb 29, 2012
 * Time: 1:53:19 PM
 * com.mycompany.inventory.persistence.
 */
public class StorageInfoDAO extends AbstractDao<StorageInfo>{
	
	private Connection con = this.connect;
	public static final String DELETE_SQL = "DELETE FROM oce_storage_info WHERE storageInfoId = ?";
	public static final String SELECT_ALL_SQL = "select * from oce_storage_info";
	public static final String SELECT_SQL = "select * from oce_storage_info where storageInfoId = ?";
	public static final String INSERT_SQL = "INSERT INTO oce_storage_info (storageInfoId,barcode,inventoryId,reorder_point,reorder_number,standardUoM,saleUoM,purchaseUoM,length,width,height,weight,manufactuer,madein,sale_conversion1,sale_conversion2,purchase_conversion1,purchase_conversion2) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	public static final String UPDATE_SQL = "UPDATE oce_storage_info SET barcode = ?, reorder_point = ?, reorder_number = ?, length = ?, width = ?, height = ?, weight = ?, manufactuer = ?, madein = ? ,sale_conversion1 = ?,sale_conversion2 = ?,purchase_conversion1 = ?,purchase_conversion2 = ? WHERE storageInfoId = ? ";
	
	 
	
	public StorageInfo create(StorageInfo storage){
		try{
			con.setAutoCommit(false);
			PreparedStatement ps = con.prepareStatement(INSERT_SQL, PreparedStatement.RETURN_GENERATED_KEYS);

			long id = 0l;
			ps.setLong(1,id);
			ps.setString(2, storage.getBarcode());
			if(storage.getInventoryId().equals("")){
				ps.setLong(3, 0);
			}else{
				ps.setLong(3, Long.parseLong(storage.getInventoryId()));
			}
			if(storage.getReorder_point().equals("")){
				ps.setLong(4, 0);
			}else{
				ps.setLong(4, Long.parseLong(storage.getReorder_point()));
			}
			if(storage.getReorder_number().equals("")){
				ps.setLong(5, 0);
			}else{
				ps.setLong(5, Long.parseLong(storage.getReorder_number()));	
			}	
			ps.setString(6, storage.getStandardUoM());
			ps.setString(7, storage.getSaleUoM());
			ps.setString(8, storage.getPurchaseUoM());					
			ps.setString(9, storage.getLength());
			ps.setString(10, storage.getWidth());
			ps.setString(11, storage.getHeight());
			ps.setString(12, storage.getWeight());
			ps.setString(13, storage.getManufactuer());
			ps.setString(14, storage.getMadein());
			ps.setString(15, storage.getSaleConversion());
			ps.setString(16, storage.getSaleConversion1());
			ps.setString(17, storage.getPurchaseConversion());
			ps.setString(18, storage.getPurchaseConversion1());
			ConnectionDatabase.executeUpdate(ps);	
			ResultSet rs=ps.getGeneratedKeys();
            if(rs.next()){
                Integer Id = (Integer) rs.getInt(1);               
                storage.setId(String.valueOf(Id));
            }
			ps.close();			
			
		}catch (Exception e) {
			// TODO: handle exception
			try{
				con.rollback();
			}catch (SQLException e1){}
			storage = null;
		}finally {
			try{
				con.setAutoCommit(true);
			}catch (SQLException e1){}
		}
		return storage;
	}
	
	public StorageInfo update(StorageInfo storage){
		try{
			
			if(storage == null || storage.getId() == null || storage.getId().equals(""))
				return storage;
						
			PreparedStatement ps = con.prepareStatement(UPDATE_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setString(1, storage.getBarcode());
			ps.setLong(2, Long.parseLong(storage.getReorder_point()));
			ps.setLong(3, Long.parseLong(storage.getReorder_number()));			
			ps.setString(4, storage.getLength());
			ps.setString(5, storage.getWidth());
			ps.setString(6, storage.getHeight());
			ps.setString(7, storage.getWeight());
			ps.setString(8, storage.getManufactuer());
			ps.setString(9, storage.getMadein());
			ps.setString(10, storage.getSaleConversion());			
			ps.setString(11, storage.getSaleConversion1());
			ps.setString(12, storage.getPurchaseConversion());
			ps.setString(13, storage.getPurchaseConversion1());
			
			ps.setLong(14, Long.parseLong(storage.getId()));
			ConnectionDatabase.executeUpdate(ps);	
			ps.close();
			
		}catch (Exception e) {
			// TODO: handle exception
			storage = null;
		}
		return storage;
	}
	
	public StorageInfo find(long id){
		List<StorageInfo> storage = new ArrayList<StorageInfo>();
		try{
			PreparedStatement ps = con.prepareStatement(SELECT_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setLong(1,id);
			storage = convert(ConnectionDatabase.executeQuery(ps));
		}catch (Exception e) {
			// TODO: handle exception
		}
		return storage.size()== 0 ? null : storage.get(0);
	}
	
	public List<StorageInfo> findAll(){
		List<StorageInfo> storage = new ArrayList<StorageInfo>();
		try{
			storage = convert(ConnectionDatabase.executeQuery(con.prepareStatement(SELECT_ALL_SQL, PreparedStatement.RETURN_GENERATED_KEYS)));
		}catch (Exception e) {
			// TODO: handle exception
			storage = null;
		}
		return storage;
	}
	
	public void delete(StorageInfo obj){
		try{
			PreparedStatement ps = con.prepareStatement(DELETE_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setLong(1,Long.parseLong(obj.getId()));
			ConnectionDatabase.executeUpdate(ps);	
			ps.close();		
		}catch (Exception e) {
			obj = null;
		}
	}
	
	private List<StorageInfo> convert(ResultSet rs) throws SQLException {
		List<StorageInfo> storage = new ArrayList<StorageInfo>();
		while (rs.next()) {
	        // Get the data from the row using the column index
			String id = rs.getString(1);
			String barcode = rs.getString(2);
			String inventoryId = rs.getString(3);
			String reorder_point = rs.getString(4);
			String reorder_number = rs.getString(5);
			String standardUoM = rs.getString(6);
			String saleUoM = rs.getString(7);
			String purchaseUoM = rs.getString(8);
			String length = rs.getString(9);
			String width = rs.getString(10);
			String height = rs.getString(11);
			String weigh = rs.getString(12);
			String manufactuer = rs.getString(13);
			String madein = rs.getString(14);
			String saleConversion = rs.getString(15);
			String saleConversion1 = rs.getString(16);
			String purchaseConversion = rs.getString(17);
			String purchaseConversion1 = rs.getString(18); 
	        storage.add(new StorageInfo(id, barcode, inventoryId, reorder_point, reorder_number, standardUoM, saleUoM, purchaseUoM, length, width, height, weigh, manufactuer, madein, saleConversion, saleConversion1, purchaseConversion, purchaseConversion1));
	    }
		rs.close();
		return storage;
	}
}
